package com.huabo.contract.mysql.mapper;

import com.hbfk.util.DateUtil;
import com.hbfk.util.PageInfo;
import com.huabo.contract.mysql.entity.TblCounterpartBankinfoMySql;

public class TblCounterpartBankinfoMySqlMapperSqlConfig {


    public String findListByPageInfo(PageInfo<TblCounterpartBankinfoMySql> pageInfo, TblCounterpartBankinfoMySql bank) {
        StringBuffer sbSql = new StringBuffer("SELECT * FROM ( SELECT BUDGET.*,ROWNUM RNUM FROM (SELECT * FROM TBL_COUNTERPART_BANKINFO WHERE 1 = 1");
        if (bank.getBudgetid() != null) {
            sbSql.append(" AND BUDGETID = " + bank.getBudgetid());
        }
        if (bank.getBankstatus() != null) {
            sbSql.append(" AND BANKSTATUS = " + bank.getBankstatus());
        }
        if (bank.getBankaccount() != null && !"".equals(bank.getBankaccount())) {
            sbSql.append(" AND BANKACCOUNT LIKE '%" + bank.getBankaccount() + "%'");
        }
        if (bank.getBankaccname() != null && !"".equals(bank.getBankaccname())) {
            sbSql.append(" AND BANKACCNAME LIKE '%" + bank.getBankaccname() + "%'");
        }

        sbSql.append(" ) BUDGET WHERE rownum <= "+(pageInfo.getCurrentPage()*pageInfo.getPageSize())+" ) WHERE RNUM > "+pageInfo.getCurrentRecord());
        String sql = sbSql.toString();
        return sql;
    }
    public String selectCountByPageInfoCount(TblCounterpartBankinfoMySql bank) {
        StringBuffer sbSql = new StringBuffer("SELECT COUNT(*) FROM TBL_COUNTERPART_BANKINFO WHERE 1 = 1");
        if (bank.getBudgetid() != null) {
            sbSql.append(" AND BUDGETID = " + bank.getBudgetid());
        }
        if (bank.getBankstatus() != null) {
            sbSql.append(" AND BANKSTATUS = " + bank.getBankstatus());
        }
        if (bank.getBankaccount() != null && !"".equals(bank.getBankaccount())) {
            sbSql.append(" AND BANKACCOUNT LIKE '%" + bank.getBankaccount() + "%'");
        }
        if (bank.getBankaccname() != null && !"".equals(bank.getBankaccname())) {
            sbSql.append(" AND BANKACCNAME LIKE '%" + bank.getBankaccname() + "%' ");
        }

        String sql = sbSql.toString();
        return sql;
    }

    public String saveBank(TblCounterpartBankinfoMySql bank) {
        StringBuffer column = new StringBuffer("INSERT INTO TBL_COUNTERPART_BANKINFO (BANKID");
        StringBuffer value = new StringBuffer(" VALUES (HIBERNATE_SEQUENCE.nextval");

        if(bank.getBankaccount() != null) {
            column.append(",BANKACCOUNT");
            value.append(",'"+bank.getBankaccount()+"'");
        }
        if(bank.getBankaccname() != null) {
            column.append(",BANKACCNAME");
            value.append(",'"+bank.getBankaccname()+"'");
        }
        if(bank.getBankkhyh() != null) {
            column.append(",BANKKHYH");
            value.append(",'"+bank.getBankkhyh()+"'");
        }
        if(bank.getBanktype() != null) {
            column.append(",BANKTYPE");
            value.append(",'"+bank.getBanktype()+"'");
        }
        if(bank.getBanknature() != null) {
            column.append(",BANKNATURE");
            value.append(",'"+bank.getBanknature()+"'");
        }
        if(bank.getBankstatus() != null) {
            column.append(",BANKSTATUS");
            value.append(",'"+bank.getBankstatus()+"'");
        }
        if(bank.getCreatestaff() != null) {
            column.append(",CREATESTAFF");
            value.append(",'"+bank.getCreatestaff()+"'");
        }
        if(bank.getOutsideid() != null) {
            column.append(",OUTSIDEID");
            value.append(",'"+bank.getOutsideid()+"'");
        }
        if(bank.getBudgetid() != null) {
            column.append(",BUDGETID");
            value.append(",'"+bank.getBudgetid()+"'");
        }
        if(bank.getCreatetime() != null) {
            column.append(",CREATETIME");
            value.append(",TO_DATE('"+DateUtil.parseDate(bank.getCreatetime(),"yyyy-MM-dd HH:mm:ss") +"', 'YYYY-MM-DD HH24:MI:SS')");
        }
        column.append(")");
        value.append(")");
        String sql = column.toString()+value.toString();
        return sql;
    }

    public String updateByBankId(TblCounterpartBankinfoMySql bank) {
        StringBuffer sql = new StringBuffer("UPDATE TBL_COUNTERPART_BANKINFO SET BANKACCOUNT = '"+bank.getBankaccount()+"'");

        if(bank.getBankaccname() != null) {
            sql.append(" ,BANKACCNAME = '"+bank.getBankaccname()+"'");
        }
        if(bank.getBankkhyh() != null) {
            sql.append(" ,BANKKHYH = '"+bank.getBankkhyh()+"'");
        }
        if(bank.getBanktype() != null) {
            sql.append(" ,BANKTYPE = '"+bank.getBanktype()+"'");
        }
        if(bank.getBanknature() != null) {
            sql.append(" ,BANKNATURE = '"+bank.getBanknature()+"'");
        }
        if(bank.getBankstatus() != null) {
            sql.append(" ,BANKSTATUS = '"+bank.getBankstatus()+"'");
        }
        if(bank.getCreatestaff() != null) {
            sql.append(" ,CREATESTAFF = '"+bank.getCreatestaff()+"'");
        }
        if(bank.getOutsideid() != null) {
            sql.append(" ,OUTSIDEID = '"+bank.getOutsideid()+"'");
        }
        if(bank.getBudgetid() != null) {
            sql.append(" ,BUDGETID = '"+bank.getBudgetid()+"'");
        }
        if(bank.getCreatetime() != null) {
            sql.append(" ,CREATETIME = TO_DATE('"+DateUtil.parseDate(bank.getCreatetime(),"yyyy-MM-dd HH:mm:ss") +"', 'YYYY-MM-DD HH24:MI:SS')");
        }
        sql.append(" WHERE BANKID = '"+bank.getBankid()+"'");
        return sql.toString();
    }



    public String findAllListByBankInfo(PageInfo<TblCounterpartBankinfoMySql> pageInfo) throws Exception {
    	TblCounterpartBankinfoMySql bank = pageInfo.getCondition();
        StringBuffer sbSql = new StringBuffer("SELECT * FROM ( SELECT BUDGET.*,ROWNUM RNUM FROM (SELECT * FROM TBL_COUNTERPART_BANKINFO WHERE 1 = 1");
        if (bank.getBudgetid() != null) {
            sbSql.append(" AND BUDGETID = " + bank.getBudgetid());
        }
        if(bank.getBankstatus() != null) {
            sbSql.append(" AND BANKSTATUS = " + bank.getBankstatus());
        }
        if(bank.getBankaccount() != null && !"".equals(bank.getBankaccount())) {
            sbSql.append(" AND BANKACCOUNT LIKE '%"+bank.getBankaccount()+"%'");
        }
        if(bank.getBankaccname() != null && !"".equals(bank.getBankaccname())) {
            sbSql.append(" AND BANKACCNAME LIKE '"+bank.getBankaccname()+"'");
        }
        sbSql.append(" ) BUDGET WHERE rownum <= "+(pageInfo.getCurrentPage()*pageInfo.getPageSize())+" ) WHERE RNUM > "+pageInfo.getCurrentRecord());
        String sql = sbSql.toString();
        return sql;

    }

    public String findCountListByBankInfo(PageInfo<TblCounterpartBankinfoMySql> pageInfo) throws Exception {
    	TblCounterpartBankinfoMySql bank = pageInfo.getCondition();
        StringBuffer sbSql = new StringBuffer("SELECT COUNT(*) FROM TBL_COUNTERPART_BANKINFO WHERE 1 = 1");
        if (bank.getBudgetid() != null) {
            sbSql.append(" AND BUDGETID = " + bank.getBudgetid());
        }
        if(bank.getBankstatus() != null) {
            sbSql.append(" AND BANKSTATUS = " + bank.getBankstatus());
        }
        if(bank.getBankaccount() != null && !"".equals(bank.getBankaccount())) {
            sbSql.append(" AND BANKACCOUNT LIKE '%"+bank.getBankaccount()+"%'");
        }
        if(bank.getBankaccname() != null && !"".equals(bank.getBankaccname())) {
            sbSql.append(" AND BANKACCNAME LIKE '"+bank.getBankaccname()+"'");
        }
        return sbSql.toString();

    }
}
